|
SQL Server 2017 : Read Scale-out Availability Group
2017/10/17 |
|
Configure Read Scale-out Availability Group.
This example is based on the environment like follows.
Configure 3 servers to create a cluster, one is a Primary node and other two nodes are secondary with read-only access.
-----------+-----------------------------+-----------------------------+------------
|10.0.0.51 |10.0.0.52 |10.0.0.53
+----------+-----------+ +----------+-----------+ +----------+-----------+
| [ node01.srv.world ] | | [ node02.srv.world ] | | [ node03.srv.world ] |
| (Primary) | | (Secondary) | | (Secondary) |
| SQL Server | | SQL Server | | SQL Server |
+----------------------+ +----------------------+ +----------------------+
|
| [1] | |
| [2] | Enable Always On Availability Group and create a user for mirroring on all nodes. Replace 'password' to strong one. |
|
[root@node01 ~]# /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1 SQL Server needs to be restarted in order to apply this setting. Please run 'systemctl restart mssql-server.service'. [root@node01 ~]# systemctl restart mssql-server [root@node01 ~]# sqlcmd -S localhost -U SA Password: 1> alter event session AlwaysOn_health on server with (startup_state=on); 2> go 1> create login dbm_login with password = 'password'; 2> create user dbm_user for login dbm_login; 3> go 1> exit |
| [3] | Create certificates on Primary node and copy them to other secondary nodes. Replace 'password', 'pvk_password' to strong one. |
|
[root@node01 ~]# sqlcmd -S localhost -U SA Password: 1> create master key encryption by password = 'password'; 2> create certificate dbm_certificate with subject = 'dbm'; 3> backup certificate dbm_certificate 4> to file = '/var/opt/mssql/data/dbm_certificate.cer' 5> with private key ( 6> file = '/var/opt/mssql/data/dbm_certificate.pvk', 7> encryption by password = 'pvk_password' 8> ); 9> go 1> exit[root@node01 ~]# cd /var/opt/mssql/data [root@node01 data]# scp dbm_certificate.* root@node02.srv.world:/var/opt/mssql/data/ root@node02.srv.world's password: dbm_certificate.cer 100% 667 614.7KB/s 00:00 dbm_certificate.pvk 100% 1212 1.0MB/s 00:00[root@node01 data]# scp dbm_certificate.* root@node03.srv.world:/var/opt/mssql/data/ root@node03.srv.world's password: dbm_certificate.cer 100% 667 581.6KB/s 00:00 dbm_certificate.pvk 100% 1212 935.1KB/s 00:00[root@node01 data]# ssh root@node02.srv.world 'chown mssql. /var/opt/mssql/data/dbm_certificate.*' [root@node01 data]# ssh root@node03.srv.world 'chown mssql. /var/opt/mssql/data/dbm_certificate.*' |
| [4] | Create certificates on all secondary nodes. Replace 'password' to strong one. For 'pvk_password', specify the same password you set on Primary node above. |
|
[root@node02 ~]# sqlcmd -S localhost -U SA Password: 1> create master key encryption by password = 'password'; 2> create certificate dbm_certificate authorization dbm_user 3> from file = '/var/opt/mssql/data/dbm_certificate.cer' 4> with private key ( 5> file = '/var/opt/mssql/data/dbm_certificate.pvk', 6> decryption by password = 'pvk_password' 7> ); 8> go 1> exit |
| [5] | Set Endpoint for mirroring on all nodes. |
|
[root@node01 ~]# sqlcmd -S localhost -U SA Password: 1> create endpoint [Hadr_endpoint] 2> as tcp (listener_ip = (0.0.0.0), listener_port = 5022) 3> for data_mirroring ( 4> role = all, 5> authentication = certificate dbm_certificate, 6> encryption = required algorithm aes 7> ); 8> alter endpoint [Hadr_endpoint] state = started; 9> grant connect on endpoint::[Hadr_endpoint] to [dbm_login]; 10> go 1> exit # if Firewalld is running, allow the port [root@node01 ~]# firewall-cmd --add-port=5022/tcp --permanent [root@node01 ~]# firewall-cmd --reload |
| [6] | Create Availability Group on primary node. |
|
[root@node01 ~]# sqlcmd -S localhost -U SA Password: 1> create availability group [AG01] 2> with (cluster_type = none) 3> for replica on 4> N'node01' with ( 5> endpoint_url = N'tcp://10.0.0.51:5022', 6> availability_mode = asynchronous_commit, 7> failover_mode = manual, 8> seeding_mode = automatic, 9> primary_role (read_only_routing_list=(('node02','node03'), 'node01')), 10> secondary_role ( 11> allow_connections = read_only, 12> read_only_routing_url = N'tcp://10.0.0.51:1433') 13> ), 14> N'node02' with ( 15> endpoint_url = N'tcp://10.0.0.52:5022', 16> availability_mode = asynchronous_commit, 17> failover_mode = manual, 18> seeding_mode = automatic, 19> primary_role (read_only_routing_list=(('node01','node03'), 'node02')), 20> secondary_role ( 21> allow_connections = read_only, 22> read_only_routing_url = N'tcp://10.0.0.52:1433') 23> ), 24> N'node03' with ( 25> endpoint_url = N'tcp://10.0.0.53:5022', 26> availability_mode = asynchronous_commit, 27> failover_mode = manual, 28> seeding_mode = automatic, 29> primary_role (read_only_routing_list=(('node01','node02'), 'node03')), 30> secondary_role ( 31> allow_connections = read_only, 32> read_only_routing_url = N'tcp://10.0.0.53:1433') 33> ); 34> alter availability group [AG01] grant create any database; 35> go 1> exit |
| [7] | Join in Availability Group on all secondary nodes. |
|
[root@node02 ~]# sqlcmd -S localhost -U SA Password: 1> alter availability group [AG01] join with (cluster_type = none); 2> alter availability group [AG01] grant create any database; 3> go 1> exit |
| [8] | Add Listener and Database to Availability Group on primary node. |
|
[root@node01 ~]# sqlcmd -S localhost -U SA Password: 1> alter availability group [AG01] add listener 'AG01_listener' 2> ( with ip ( 3> ('10.0.0.51', '255.255.255.0'), 4> ('10.0.0.52', '255.255.255.0'), 5> ('10.0.0.53', '255.255.255.0') ), 6> port = 1433 7> ); 8> go 1> create database [AG01_DB]; 2> alter database [AG01_DB] set recovery full; 3> backup database [AG01_DB] to disk = N'/var/opt/mssql/data/AG01_DB.bak'; 4> alter availability group [AG01] add database [AG01_DB]; 5> go Processed 304 pages for database 'AG01_DB', file 'AG01_DB' on file 1. Processed 3 pages for database 'AG01_DB', file 'AG01_DB_log' on file 1. BACKUP DATABASE successfully processed 307 pages in 0.163 seconds (14.714 MB/sec). 1> exit # verify possible to see the database on secondary nodes [root@node01 ~]# sqlcmd -S node02 -U SA -Q 'select name, create_date from sys.databases where name = "AG01_DB"' Password: name create_date ---------------------------- ----------------------- AG01_DB 2017-10-18 19:20:18.740 (1 rows affected) |
| [9] | It's OK all. Verify it works normally to access to the listener and database. |
|
[root@node01 ~]# sqlcmd -S localhost -U SA Password: # current state 1> select L.replica_id,L.replica_server_name,R.role_desc from sys.availability_replicas as L 2> left join sys.dm_hadr_availability_replica_states as R on L.replica_id = R.replica_id 3> go replica_id replica_server_name role_desc ------------------------------------ ----------------------- ---------------- ACDB4168-C9FD-493F-89C0-FBE067E85BA5 node01 PRIMARY E8B30CD1-BB51-4314-AC39-C3326ECA67C2 node02 SECONDARY A08550D5-BD92-4A4A-9678-9CC962E37431 node03 SECONDARY (3 rows affected) 1> exit[root@node01 ~]# sqlcmd -S 10.0.0.51 -U SA -K readonly -d AG01_DB -Q 'select @@servername' Password: ------------- node03 (1 rows affected)[root@node01 ~]# sqlcmd -S 10.0.0.51 -U SA -K readonly -d AG01_DB -Q 'select @@servername' Password: ------------- node02 (1 rows affected)[root@node01 ~]# sqlcmd -S 10.0.0.51 -U SA -K readonly -d AG01_DB -Q 'select @@servername' Password: -------------- node03 (1 rows affected) # if access to secondary nodes not with read-only, it is denied like follows [root@node01 ~]# sqlcmd -S 10.0.0.52 -U SA -d AG01_DB -Q 'select @@servername' Password:
Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : The target database ('AG01_DB') is in an availability
group and is currently accessible for connections when the application intent is set to read only.
For more information about application intent, see SQL Server Books Online..
|
| [10] | If you'd like to switch primary to another node, run like follows. For example, make node02 be primary. |
|
[root@node01 ~]# sqlcmd -S localhost -U SA Password: # current state 1> select L.replica_id,L.replica_server_name,R.role_desc from sys.availability_replicas as L 2> left join sys.dm_hadr_availability_replica_states as R on L.replica_id = R.replica_id 3> go replica_id replica_server_name role_desc ------------------------------------ ----------------------- ---------------- ACDB4168-C9FD-493F-89C0-FBE067E85BA5 node01 PRIMARY E8B30CD1-BB51-4314-AC39-C3326ECA67C2 node02 SECONDARY A08550D5-BD92-4A4A-9678-9CC962E37431 node03 SECONDARY (3 rows affected) # change availability_mode 1> alter availability group [AG01] modify replica 2> on N'node01' with (availability_mode = synchronous_commit); 3> go 1> alter availability group [AG01] modify replica 2> on N'node02' with (availability_mode = synchronous_commit); 3> go 1> alter availability group [AG01] modify replica 2> on N'node03' with (availability_mode = synchronous_commit); 3> go # change role to secondary 1> alter availability group [AG01] set (role = secondary); 2> go 1> exit # move to the node you'd like to make it primary and run failover [root@node02 ~]# sqlcmd -S localhost -U SA Password: 1> alter availability group [AG01] force_failover_allow_data_loss; 2> go # change availability_mode 1> alter availability group [AG01] modify replica 2> on N'node01' with (availability_mode = asynchronous_commit); 3> go 1> alter availability group [AG01] modify replica 2> on N'node02' with (availability_mode = asynchronous_commit); 3> go 1> alter availability group [AG01] modify replica 2> on N'node03' with (availability_mode = asynchronous_commit); 3> go # go to all secondary nodes and resume database mirroring [root@node01 ~]# sqlcmd -S localhost -U SA Password: 1> alter database [AG01_DB] set hadr resume; 2> go # goto primary node and show current state [root@node02 ~]# sqlcmd -S localhost -U SA Password: 1> select L.replica_id,L.replica_server_name,R.synchronization_state_desc,R.synchronization_health_desc 1> from sys.availability_replicas as L 2> left join sys.dm_hadr_database_replica_states as R on L.replica_id = R.replica_id 3> go replica_id replica_server_name synchronization_state_desc synchronization_health_.. ------------------------------------ -------------------- --------------------------- ------------------------- ACDB4168-C9FD-493F-89C0-FBE067E85BA5 node01 SYNCHRONIZING HEALTHY E8B30CD1-BB51-4314-AC39-C3326ECA67C2 node02 SYNCHRONIZED HEALTHY A08550D5-BD92-4A4A-9678-9CC962E37431 node03 SYNCHRONIZING HEALTHY (3 rows affected) |
| [11] | It's possible to verify or change state of [Always On AG] with SSMS on Windows client. |
|